# -*- coding: utf-8 -*-
"""
数据库模型定义
"""
import sqlite3
import os
from datetime import datetime
from utils.path_manager import get_database_file

class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.db_path = get_database_file()
        self._init_database()
    
    def _init_database(self):
        """初始化数据库表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建数据库连接配置表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS db_connections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                host TEXT NOT NULL,
                port INTEGER NOT NULL,
                database_name TEXT NOT NULL,
                username TEXT NOT NULL,
                encrypted_password TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建导出日志表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS export_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                export_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                database_name TEXT NOT NULL,
                table_count INTEGER NOT NULL,
                export_format TEXT NOT NULL,
                file_path TEXT NOT NULL,
                file_name TEXT NOT NULL,
                export_result TEXT NOT NULL,
                error_message TEXT,
                user_id INTEGER DEFAULT 1
            )
        ''')
        
        conn.commit()
        conn.close()
    
    def get_connection(self):
        """获取数据库连接"""
        return sqlite3.connect(self.db_path)
    
    def save_db_connection(self, name, host, port, database_name, username, encrypted_password):
        """保存数据库连接配置"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                INSERT OR REPLACE INTO db_connections 
                (name, host, port, database_name, username, encrypted_password, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (name, host, port, database_name, username, encrypted_password, datetime.now()))
            
            conn.commit()
            return True
        except Exception as e:
            print(f"保存数据库连接配置失败: {e}")
            return False
        finally:
            conn.close()
    
    def get_db_connections(self):
        """获取所有数据库连接配置"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, name, host, port, database_name, username, created_at, updated_at
            FROM db_connections
            ORDER BY updated_at DESC
        ''')
        
        results = cursor.fetchall()
        conn.close()
        
        return [{
            'id': row[0],
            'name': row[1],
            'host': row[2],
            'port': row[3],
            'database_name': row[4],
            'username': row[5],
            'created_at': row[6],
            'updated_at': row[7]
        } for row in results]
    
    def get_db_connection(self, connection_id):
        """获取指定数据库连接配置"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, name, host, port, database_name, username, encrypted_password
            FROM db_connections
            WHERE id = ?
        ''', (connection_id,))
        
        result = cursor.fetchone()
        conn.close()
        
        if result:
            return {
                'id': result[0],
                'name': result[1],
                'host': result[2],
                'port': result[3],
                'database_name': result[4],
                'username': result[5],
                'encrypted_password': result[6]
            }
        return None
    
    def delete_db_connection(self, connection_id):
        """删除数据库连接配置"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute('DELETE FROM db_connections WHERE id = ?', (connection_id,))
            conn.commit()
            return cursor.rowcount > 0
        except Exception as e:
            print(f"删除数据库连接配置失败: {e}")
            return False
        finally:
            conn.close()
    
    def save_export_log(self, database_name, table_count, export_format, file_path, 
                       file_name, export_result, error_message=None):
        """保存导出日志"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        try:
            cursor.execute('''
                INSERT INTO export_logs 
                (database_name, table_count, export_format, file_path, file_name, 
                 export_result, error_message)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (database_name, table_count, export_format, file_path, file_name, 
                  export_result, error_message))
            
            conn.commit()
            return True
        except Exception as e:
            print(f"保存导出日志失败: {e}")
            return False
        finally:
            conn.close()
    
    def get_export_logs(self, limit=100):
        """获取导出日志"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, export_time, database_name, table_count, export_format, 
                   file_path, file_name, export_result, error_message
            FROM export_logs
            ORDER BY export_time DESC
            LIMIT ?
        ''', (limit,))
        
        results = cursor.fetchall()
        conn.close()
        
        return [{
            'id': row[0],
            'export_time': row[1],
            'database_name': row[2],
            'table_count': row[3],
            'export_format': row[4],
            'file_path': row[5],
            'file_name': row[6],
            'export_result': row[7],
            'error_message': row[8]
        } for row in results]
